********************************************************************************************************************************
***   Replication file for:                                                                                                  ***
***   Berbee, P., Braun, S. T. and Franke, R. (2024). Reversing Fortunes of German Regions, 1926-2019. JoEG.			     ***
***   							                                                                                             ***
***   SCRIPT: 	_x6_preparation_misc.do																						 ***	
***   PURPOSE: 	Reads in and prepares miscellaneous data 																 	 ***
********************************************************************************************************************************

* Preamble (unnecessary when executing run.do)
run "$reversing/scripts/programs/_config.do"

************
* Code begins
************

*** Predevelopment index

** Import data
use "$reversing/data/cities_LLM.dta", clear

** Save data
save "$reversing/processed/intermediate/preindustrial.dta", replace


*** Patent data 1877-2020

** Import data in 5-year intervals
import excel "$reversing/data/data_patents_5y_1878_2016.xlsx", firstrow clear
rename Sheet1__La labor_market_id
save "$reversing/processed/intermediate/patents_5y.dta", replace



*** Market access loss

** Import data 
import delimited "$reversing/data/Lost_Firm_Market_Access.csv", clear

rename sheet1__la labor_market_id
rename lostfma market_access_lost_1945
label var market_access_lost_1945 "Market access lost 1945 (Sturm and Redding, 2008)"

** Save data
drop v1
save "$reversing/processed/intermediate/malost1945.dta", replace



*** Political outcomes: 1957 election

** Import data 
import excel "$reversing/data/Bundestagswahl_1957.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id

gen voteshare_SPD_1957 = ZSPD / Zinsg

label var voteshare_SPD_1957 "SPD vote share in 1957 national election"

** Save data
keep  labor_market_id voteshare_SPD_1957
save "$reversing/processed/intermediate/election1957.dta", replace



*** Rubble per capita 1946

** Import data 
import excel "$reversing/data/Rubble.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id

** Save data
keep labor_market_id rubble_unloosened 
save "$reversing/processed/intermediate/rubble.dta", replace



*** Schengen area

** Import data 
import excel "$reversing/data/Schengen_Border.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id

** Save data
keep labor_market_id DummySchengen1995 DistSchengen1995 DummySchengen2008 DistSchengen2008

rename DummySchengen1995 d_schengen_1995
label var d_schengen_1995 "Location at Schengen border 1995 (0/1)" 

rename DistSchengen1995 dist_schengen_1995
label var dist_schengen_1995 "Distance to Schengen area border, 2008"

rename DummySchengen2008 d_schengen_2008
label var d_schengen_2008 "Location at Schengen border 2008 (0/1)" 

rename DistSchengen2008 dist_schengen_2008
label var dist_schengen_2008 "Distance to Schengen area border, 2008"

save "$reversing/processed/intermediate/schengen.dta", replace



*** Students by subjects

** Import data 
import excel "$reversing/data/Students_by_field_2019_20_LLM.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
destring labor_market_id, replace

gen students_humanities_2019 =  humanities + art
label var students_humanities_2019 "Number of students in the humanities (incl. arts), 2019"

rename law_econ_social students_law_econ_2019
label var  students_law_econ_2019 "Number of students in law, economics, and the social sciences, 2019"

rename math_nat_science students_math_natsc_2019
label var students_math_natsc_2019 "Number of students in mathematics and natural sciences, 2019"

rename medicine students_medicine_2019
label var students_medicine_2019 "Number of students in medicine, 2019"

rename engineering students_engineer_2019
label var students_engineer_2019 "Number of students in engineering, 2019"

gen students_other_2019 = sports + agri_forest_nutrition_vet + other_field
label var students_other_2019 "Number of students in other fields, 2019"

rename students_total students_total_2019
label var students_total_2019 "Total number of students, 2019"

keep labor_market_id students*

save "$reversing/processed/intermediate/students2019.dta", replace



*** Universities

** Import data 
import excel "$reversing/data/Universities1.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
drop if labor_market_id == "integer(0)"
destring labor_market_id, replace

gen d_uni_type = 1 if Hochschultyp == "Fachhochschulen und Hochschulen ohne Promotionsrecht"
replace d_uni_type = 2 if Hochschultyp == "Kunst- und Musikhochschulen mit und ohne Promotionsrecht"
replace d_uni_type = 3 if Hochschultyp == "Universitäten und Hochschulen mit Promotionsrecht"

label define unitype 1 "Fachhochschulen and Hochschulen without the right to award doctorates" 2 "Kunst- and Musikhochschulen w/ and w/o the right to award doctorates" 3 "Universitäten and Hochschulen with the right to award doctorates"
label values d_uni_type unitype 

gen d_promotionsrecht = 0 if Promotionsrecht == "Nein"
replace  d_promotionsrecht = 1 if Promotionsrecht == "Ja"

label define yesno 0 "no" 1 "yes"
label values d_promotionsrecht yesno



*** Focus only on universities with at least 50 students today
keep if AnzahlStudierende >= 50

foreach t in 1900 1910 1920 1930 1940 1950 1960 1970 1980 1990 2000 2010 2015{
	bysort labor_market_id: egen help_`t' = count(Gründungsjahr) if Gründungsjahr <= `t'
	bysort labor_market_id: egen unis_`t' = mean(help_`t')
	label var unis_`t' "Number of universities and Hochschulen in `t'"
	drop help_`t'

	bysort labor_market_id: egen help_`t' = count(Gründungsjahr) if Gründungsjahr <= `t' & AnzahlStudierende >= 7500
	bysort labor_market_id: egen lunis_`t' = mean(help_`t')
	label var lunis_`t' "Number of larger universities and Hochschulen in `t'"
	drop help_`t'

	
	bysort labor_market_id: egen help_`t' = count(Gründungsjahr) if Gründungsjahr <= `t' & d_promotionsrecht == 1
	bysort labor_market_id: egen unis_wphd_`t' = mean(help_`t')
	label var unis_wphd_`t' "Number of universities and Hochschulen with Promotionsrecht in `t'"
	drop help_`t'
}

* Keep only one observation per labor market
sort labor_market_id
drop if labor_market_id == labor_market_id[_n-1]

keep labor_market_id unis* lunis*

save "$reversing/processed/intermediate/universities.dta", replace



*** Refugee influx 2015/16

import excel "$reversing/data/Asylum_seekers_2016_LLM.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
destring labor_market_id, replace

rename asylum_seekers_total asylum_seekers_2016
label var asylum_seekers_2016 "Number of asylum seekers in 2016"

keep labor_market_id  asylum_seekers_2016

save "$reversing/processed/intermediate/refugees2016.dta", replace



*** Teacher/pupil ratio

** Import data 
import excel "$reversing/data/Primary_Secondary_School_2018_19_LLM.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
destring labor_market_id, replace

gen pupil_teacherf_prim_2019 = pprimary / tprimaryft 
label var pupil_teacherf_prim_2019 "Pupil-teacher ratio in primary school (FTE), 2018/19"

gen pupil_teacherf_sec_2019 = psecondary / tsecondaryft 
label var pupil_teacherf_sec_2019 "Pupil-teacher ratio in secondary school (FTE), 2018/19"

gen pupil_teacher_prim_2019 = pprimary / tprimary 
label var pupil_teacher_prim_2019 "Pupil-teacher ratio in primary school, 2018/19"

gen pupil_teacher_sec_2019 = psecondary / tsecondary 
label var pupil_teacher_sec_2019 "Pupil-teacher ratio in secondary school, 2018/19"

gen pupil_teacherfte_sec_2019 = psecondary / (tsecondaryft + 0.5*(tsecondary - tsecondaryft)) 
label var pupil_teacherfte_sec_2019 "Pupil-teacher ratio in secondary school, 2018/19"


keep labor_market_id pupil_teacherf_prim_2019 pupil_teacherf_sec_2019 pupil_teacherfte_sec_2019 pupil_teacher_prim_2019 pupil_teacher_sec_2019

save "$reversing/processed/intermediate/pupil_teacher.dta", replace



*** Internal migration 

** Import data for 1950-1961
import excel "$reversing/data/Net_Migration_1950_61.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
destring labor_market_id, replace

label var net_mig_1950_61 "Net migration 1950-61"

keep labor_market_id net_mig_1950_61

save "$reversing/processed/intermediate/netmig_1950_61.dta", replace

** Import data for 1961-1970
import excel "$reversing/data/Net_Migration_1961_70.xlsx", sheet("Data") first clear

rename Sheet1__La labor_market_id
destring labor_market_id, replace

label var net_mig_1961_70 "Net migration 1961-70"

keep labor_market_id net_mig_1961_70

save "$reversing/processed/intermediate/netmig_1961_70.dta", replace

***EOF


